#!/usr/bin/env python
# coding: utf-8


import sys
from utils.mysqlutils import MysqlUtils
from config.config import configs


reload(sys)
sys.setdefaultencoding('utf8')


class DatabaseFields(object):
    """
    database fields processing
    """

    database = configs['mysql_offline']['database']
    table_name = ''
    table_type = 'BASE TABLE'

    @classmethod
    def get_all_tables(cls):
        """
        get all tables&rows name from database
        :return:
        """

        query = "select table_name, table_rows from information_schema.tables " \
                "where table_schema=%s and table_type=%s;"
        args = (cls.database, cls.table_type)

        conn = MysqlUtils.connect()
        cursor = MysqlUtils.common_query(conn=conn, query=query, args=args)
        res_rows = list()
        if cursor.rowcount:
            rows = cursor.fetchall()
            for index, row in enumerate(rows):
                res_rows.append(MysqlUtils.sql_row_to_dict(cursor, row))
        cursor.close()
        conn.close()
        return res_rows

    @classmethod
    def get_all_columns(cls, table='%'):
        """
        get all columns from table
        :param table: table='%'表示获取整个数据库的字段信息
        :return:
        """
        query = ("select table_name, column_name, column_type, column_default, column_comment "
                 "from information_schema.columns "
                 "where table_schema=%s and table_name like %s;")
        args = (cls.database, table)

        conn = MysqlUtils.connect()
        cursor = MysqlUtils.common_query(conn=conn, query=query, args=args)
        res_rows = list()
        if cursor.rowcount:
            rows = cursor.fetchall()
            for index, row in enumerate(rows):
                res_rows.append(MysqlUtils.sql_row_to_dict(cursor, row))
        cursor.close()
        conn.close()
        return res_rows

    @classmethod
    def exist_field(cls, field, table='%'):
        """
        判断某个字段是否存在某个表或者数据库
        :param field:
        :param table:
        :return: 若存在返回具体信息，否则返回False
        """
        exists = list()
        all_fields = cls.get_all_columns(table)
        for index, fields in enumerate(all_fields):
            if field == fields["column_name"]:
                exists.append(fields)
        return exists if exists else False


if __name__ == '__main__':

    table_name = 'company_profit_sheet'
    results = DatabaseFields.get_all_columns(table_name)
    for i, r in enumerate(results):
        if r['column_type'] == 'decimal(18,2)':
            alter_sql = 'alter table {0} add column {1} decimal(18, 4)  comment \'{2}\';\n'\
                .format(table_name+'_yoy', r['column_name'], r['column_comment'])
            open('/home/sql08/{0}.sql'.format(table_name+'_yoy'), 'a').write(alter_sql)
            # print alter_sql,
